package com.ihai.spring.boot.sample.gp;

import org.postgresql.PGConnection;
import org.postgresql.copy.PGCopyOutputStream;

import java.io.DataOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

/**
 * @author Adolphus
 * @description TODO
 * @date 2021/11/9 9:20
 */
public class Application {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.137.122:5432/richsyncdb", "richsync", "richsync");
        conn.setAutoCommit(false);

        Statement statement = conn.createStatement();
        statement.execute("truncate table public.t_test_a");
        System.out.println("start statement");
        long start = System.currentTimeMillis();
        int count = 0;
        StringBuilder sql = new StringBuilder("COPY public.t_test_a(id) FROM STDIN BINARY");
        PGCopyOutputStream pgCopyOutputStream = new PGCopyOutputStream((PGConnection) conn, sql.toString());
        DataOutputStream os = new DataOutputStream(pgCopyOutputStream);
        byte[] headerSignature = {'P', 'G', 'C', 'O', 'P', 'Y', '\n', (byte) 0xFF, '\r', '\n', '\0'};
        os.write(headerSignature);
        // 32 bit integer 文件头部标志位，无OID
        os.writeInt(0);
        // 32 bit header 文件头部扩展区域
        os.writeInt(0);
        for(int i=0;i<=1000000;i++){
            os.writeShort(1);
            os.writeInt(8);
            os.writeLong(i);
            count++;
        }
        os.writeShort(-1);
        os.flush();
        os.close();
        conn.commit();
        System.out.println("in:"+(count*1000/(System.currentTimeMillis()-start)));
//        start = System.currentTimeMillis();
//        for(int i=count;i>=0;i--){
//            statement.addBatch("update public.t_test_a set b3='3' where id='" + i + "'");
//            if(i%100==0){
//                statement.executeBatch();
//            }
//        }
//        conn.commit();
//        System.out.println("update:"+(count*1000/(System.currentTimeMillis()-start)));
//        start = System.currentTimeMillis();
//        String in = "";
//        for(int i=count;i>=0;i--){
//            if(i != 0){
//                in+= "'"+i+"1true1',";
//            }else{
//                in+="'"+i+"1true1'";
//            }
//        }
//        statement.execute("delete from public.t_test_a where id||b3||b4||b3 in (" + in + ")");
//        conn.commit();
//        System.out.println("delete:"+(count*1000/(System.currentTimeMillis()-start)));

        start = System.currentTimeMillis();
        statement.execute("create TEMPORARY  table  t_test_a_temp as (select * from public.t_test_a where 1!=1)");
        sql = new StringBuilder("COPY t_test_a_temp(id) FROM STDIN BINARY");
        pgCopyOutputStream = new PGCopyOutputStream((PGConnection) conn, sql.toString());
        os = new DataOutputStream(pgCopyOutputStream);
        os.write(headerSignature);
        // 32 bit integer 文件头部标志位，无OID
        os.writeInt(0);
        // 32 bit header 文件头部扩展区域
        os.writeInt(0);
        for(int i=0;i<=count;i++){
            os.writeShort(1);
            os.writeInt(8);
            os.writeLong(i);
        }
        os.writeShort(-1);
        os.flush();
        os.close();
        conn.commit();
        statement.execute("delete from public.t_test_a where id in (select id from t_test_a_temp)");
        conn.commit();
        System.out.println("copy delete:"+(count*1000/(System.currentTimeMillis()-start)));

//        start = System.currentTimeMillis();
//        PreparedStatement preparedStatement = conn.prepareStatement("delete from public.t_test_a where id=?");
//        for(int i=count;i>=0;i--){
//            preparedStatement.setInt(1,i);
//            preparedStatement.addBatch();
//            if(i%100==0){
//                preparedStatement.executeBatch();
//            }
//        }
//        preparedStatement.executeBatch();
//        conn.commit();
//        System.out.println("delete:"+(count*1000/(System.currentTimeMillis()-start)));

//        String sql = "INSERT INTO public.t_test_a (id, b1, b2, b3, b4, b5, b6, b7, c11, b8, b9) VALUES(?, NULL, NULL, '1', true, '2021-10-20 19:35:29.000', '2021-10-20', NULL, NULL, NULL, NULL)";
//        System.out.println("start preparedStatement");
//        start = System.currentTimeMillis();
//        PreparedStatement preparedStatement = conn.prepareStatement(sql);
//        for(int j=0;j<1000;j++) {
//            for (int i = 0; i < 100; i++) {
//                preparedStatement.setInt(1, count++);
//                preparedStatement.addBatch();
//            }
//            preparedStatement.executeBatch();
//            conn.commit();
//        }
//        System.out.println("preparedStatement:"+(count*500/(System.currentTimeMillis()-start)));
    }
}
